package de.hundezimmer.db;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TreeMap;
import java.util.Vector;

import de.hundezimmer.beans.Customer;
import de.hundezimmer.beans.Dog;

public class DBCustomer implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 4182342048332468863L;
	private Connection conn;

	public DBCustomer(Connection conn) {
		this.conn = conn;
	}

	public void deleteCustomer(int customerid) {

	}

	public TreeMap<Customer, Vector<Dog>> getAllCustomerDogs() throws SQLException {
		TreeMap<Customer, Vector<Dog>> results = new TreeMap<Customer, Vector<Dog>>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM customer as c join customer_has_dog as cd on (c.id = cd.user_id) join dog as d on (cd.hund_id = d.id)");

		while (rs.next()) {
			Customer c = new Customer(rs.getInt("id"), rs.getString("email"), rs.getString("vorname"), rs.getString("name"), rs.getDate("create_time"));
			Vector<Dog> dogs = new Vector<Dog>();
			dogs.add(new Dog(rs.getInt("d.id"), rs.getString("d.name"), rs.getInt("d.typ"), rs.getString("comments"), rs.getDate("birthday")));
			if (results.containsKey(c)) {
				results.get(c).addAll(dogs);
			} else {
				results.put(c, dogs);
			}
		}

		return results;
	}

	public Vector<Customer> getAllCustomers() throws SQLException {
		Vector<Customer> results = new Vector<Customer>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM customer");

		while (rs.next()) {
			results.add(new Customer(rs.getInt("id"), rs.getString("email"), rs.getString("vorname"), rs.getString("name"), rs.getDate("create_time")));
		}

		return results;
	}

	public Customer getCustomer(int customerid) throws SQLException {
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM customer where id=" + customerid);

		while (rs.next()) {
			return new Customer(rs.getInt("id"), rs.getString("email"), rs.getString("vorname"), rs.getString("name"), rs.getDate("create_time"));
		}
		return null;
	}

	public Vector<Dog> getCustomerDogs(int customerid) throws SQLException {
		Vector<Dog> results = new Vector<Dog>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM customer as c join customer_has_dog as cd on (c.id = cd.user_id) join dog as d on (cd.hund_id = d.id) where c.id="
				+ customerid);

		while (rs.next()) {
			results.add(new Dog(rs.getInt("d.id"), rs.getString("d.name"), rs.getInt("d.typ"), rs.getString("comments"), rs.getDate("birthday")));
		}

		return results;
	}

	public void insertCustomer(Customer customer) throws SQLException {
		Statement stmt = conn.createStatement();
		// INSERT INTO `hundezimmer`.`customer` (`email`, `vorname`, `name`, `create_time`) VALUES ('asd', 't', 'b', '2014-07-02 17:57:50');
		stmt.executeUpdate("INSERT INTO customer (email, vorname, name, create_time) VALUES(" + "'" + customer.getEmail() + "'" + ", '" + customer.getVname() + "'" + ", '"
				+ customer.getName() + "'" + ", '" + customer.getCreate_time() + "'" + ")");
	}

	public void updateCustomer(Customer customer) {

	}
}
